# coding=utf-8
from datetime import *
from pandas.tseries.offsets import Minute, BDay
import numpy as np
import pandas as pd

print("201606"[4:6] == '06')

def calc_netvalue_in_period(df):
    diff_df = df.diff(1)
    def trans_row(row):
        if row.name[4:6] == '03':
            return df.loc[row.name]
        else:
            return row

    df = diff_df.apply(trans_row, axis="columns")
    return df

def test_mean():
    print(">>>test_mean")
    df = pd.DataFrame([[1, 4], [2, 4], [3, 4], [4, 10]],
                      index=['201503', '201506', '201509', '201512'],
                      columns=['income', 'cost'])
    print df
    print df.mean()

test_mean()

###test
###
###
def test_series_calc_netvalue_in_period():
    print(">>>test_series_calc_netvalue_in_period")
    df = pd.DataFrame([[-1, 4], [np.nan, 4], [0, 4], [2, 4], [5, 4], [6, 4], [7, 4], [8, 4]],
                      index=['201503', '201506', '201509', '201512', '201603', '201606', '201609', '201612'],
                      columns=['income', 'cost'])
    print df
    netvalue_diff = calc_netvalue_in_period(df)
    df['income_in_period'] = netvalue_diff['income']
    print df

test_series_calc_netvalue_in_period()


def test_ttm_value():
    print(">>>test_ttm_value")
    df = pd.DataFrame([[-1, 4], [2, 4], [0, 4], [2, 4], [5, 4], [6, 4], [7, 4], [8, 4]],
                      index=['201503', '201506', '201509', '201512', '201603', '201606', '201609', '201612'],
                      columns=['income', 'cost'])
    print df
    netvalue_in_period_df = calc_netvalue_in_period(df)
    print netvalue_in_period_df
    ttm_df = netvalue_in_period_df.rolling(4).sum()
    print ttm_df

test_ttm_value()

def test_shift():
    print ">>>test_shift_issue"
    df = pd.DataFrame([[-1, 4], [None, 4], [np.nan, np.nan], [2, 4], [None, 4], [6, 4], [7, 4], [8, 4]],
                      index=['2015-03', '2015-06', '2015-09', '2015-12', '2016-03', '2016-06', '2016-09', '2016-12'],
                      columns=['income', 'cost'])
    print df
    print df.shift(-1, axis="index")

test_shift()


def zero_to_nan_for_df(df):
    #return df.applymap(lambda x: None if x == 0 else x)
    return df.replace({0: np.nan})


def calc_stock_percent_change(df):
    tmp = df.applymap(lambda x: 1 if x > 0 else -1).shift(4)
    df = zero_to_nan_for_df(df)
    percent_df = df.pct_change(periods=4, fill_method=None) * tmp
    return percent_df

def test_calc_stock_percent_change():
    print ">>>test_calc_stock_percent_change"
    df = pd.DataFrame([[-1, 4], [np.nan, 4], [0, 4], [2, 4], [5, 4], [6, 4], [7, 4], [8, 4]],
                      index=['2015-03', '2015-06', '2015-09', '2015-12', '2016-03', '2016-06', '2016-09', '2016-12'],
                      columns=['income', 'cost'])
    print df
    print calc_stock_percent_change(df)

test_calc_stock_percent_change()

def test_diff_percent_change_issue():
    print ">>>diff_percent_change_issue"
    '''dataframe.percent_change fill_method是默认值 对于有NaN的会有问题,
     See the percentage change in a Series where filling NAs with last valid observation forward to next valid.
     参考：pandas.DataFrame.fillna
    dataframe.diff没有问题'''
    df = pd.DataFrame([[-1, 4], [np.nan, 4], [0, 4], [2, 4], [5, 4], [6, 4], [7, 4], [8, 4]],
                      index=['2015-03', '2015-06', '2015-09', '2015-12', '2016-03', '2016-06', '2016-09', '2016-12'], columns=['income', 'cost'])
    print df
    print df.diff(4)
    print ("pct_change:")
    print df.pct_change(4, fill_method=None) ###如果fill_method !＝None，注意2016-09 有问题

test_diff_percent_change_issue()



def test_calc_netvalue_in_period():
    df = pd.DataFrame([[-1, 4], [2, 4], [3, 4], [2, 4], [5, 4], [6, 4], [7, 4], [8, 4]],
                      index=['201503', '201506', '201509', '201512', '201603', '201606', '201609', '201612'],
                      columns=['income', 'cost'])
    print ">>>calc_netvalue_in_period"
    print df
    result_df = calc_netvalue_in_period(df)
    print result_df

test_calc_netvalue_in_period()


temp = pd.DataFrame({'id':[1,1,1,2,2,3],'value':[1,2,3,4,5,6]});

print pd.isnull(temp)
print temp.where(temp == 1, np.nan)

##good article: https://blog.csdn.net/hhtnan/article/details/80080240

df = pd.DataFrame([[1, np.nan, None, 4, 5, 6, 7, 8], [2, 4, 6, 8, 10, 12, 14, 16]], index=['income', 'cost'],
                  columns=['2015-03', '2015-06', '2015-09', '2015-12', '2016-03', '2016-06', '2016-09', '2016-12'])
print df
print df.shift(-1, axis=1)

print df.replace({-2:np.nan})
print df.replace({np.nan: 111})
print df.replace({None: 111})
print df.replace(np.nan, 111)
print (df.rolling(2, axis=1).sum().values)
pct_change_df = df.pct_change(periods=1, axis=1)
print(df.pct_change(periods=1, axis=1))

print df.shift(1, axis=1)

print "===stock percent change==="
#获取percent_change
some_cloumn_df = df[['2015-03', '2015-06']]
print some_cloumn_df
print "====shift ==="
print some_cloumn_df.shift(1, axis=1)
print some_cloumn_df.shift(1, axis=1).applymap(lambda x: 1 if x > 0 else -1)
boolean_df = some_cloumn_df.pct_change(periods=1, axis=1) * \
             some_cloumn_df.shift(1, axis=1).applymap(lambda x: 1 if x > 0 else -1)
print boolean_df


df[['2015-03', '2015-06']] = boolean_df
print ("------  print df  --------")
print df
#print (df.shift(3, axis=1))
shift_df = df.shift(3, axis=1)
print ("------  shift_df  --------")
print shift_df
shift_df[shift_df.columns[0:3]] = df[df.columns[0:3]]
print ("------  shift_df add back first 3 columns --------")
print shift_df
print (df + df.shift(3, axis=1))
# axis =1 沿着列的方向，步长为2的增长百分百，for example： cell[0][i+1] - cell[0][i]/cell[0][i]
print(df.pct_change(periods=1, axis=1))
# 沿着列的方向，步长为1相减所得值
print(df.diff(periods=1, axis=1))


df = pd.DataFrame([[-2, -1, 3], [2, 4, 6], [2, 4, 6], [4, 8, 10]], index=['2018-03', '2018-06', '2018-09', '2018-12'],
                  columns = ['存货', '收入', '成本'])
print df

# https://www.cnblogs.com/en-heng/p/5630849.html
# https://pandas.pydata.org/pandas-docs/stable/merging.html
df = pd.DataFrame({'total_bill': [16.99, 10.34, 23.68, 23.68, 24.59],
                   'tip': [1.01, 1.66, 3.50, 3.31, 3.61],
                   'sex': ['Female', 'Male', 'Male', 'Male', 'Female']})

df2 = pd.DataFrame({'total_bill': [16.99, 10.34, 23.68, 23.68, 24.59],
                   'tip': [1.01, 1.66, 3.50, 3.31, 3.61],
                   'sexB': ['Female', 'Male', 'Male', 'Male', 'Female']})

#print df.append(df2)
print "------"
# data type of columns
print df.dtypes
print "------"

# indexes
print df.index
# return pandas.Index
print df.columns
# each row, return array[array]
print df.values
# a tuple representing the dimensionality of df
print df.shape
print "---df[1: 3]---"
print df[1: 3]
print "---df[['total_bill', 'tip']]---"
print df[['total_bill', 'tip']]

#print df.drop_duplicates(subset=['sex'], keep='first', inplace=False)
print "---df.groupby('sex').size()---"
print df.groupby('sex').size()
print "---df.groupby('sex').count()---"
print df.groupby('sex').count()
print "---df.groupby('sex')['tip'].count()---"
print df.groupby('sex')['tip'].count()

#################https://pandas.pydata.org/pandas-docs/stable/timeseries.html############################

rng = pd.date_range('1/1/2011', periods=72, freq='H')
print rng
ts = pd.Series(np.random.randn(len(rng)), index=rng)
print ts.head()
converted = ts.asfreq('45Min', method='pad')
print converted.head()

print ts.resample('D').mean()

print pd.Period('2012-05', freq='D')

periods = [pd.Period('2012-01'), pd.Period('2012-02'), pd.Period('2012-03')]

ts = pd.Series(np.random.randn(3), periods)
print ts

start = datetime(2016, 06, 29)
end = datetime(2012, 1, 1)
index = pd.date_range(start, end)
print index

print pd.date_range(start, periods=5, freq=BDay())

df2 = pd.DataFrame(
    {'FirmID': pd.Series(['ID001', 'ID001', 'ID001', 'ID001', 'ID001', 'ID001', 'ID001', 'ID001', 'ID001', 'ID001']),
     'RSSD9999': pd.Series(
         [20060331, 20060630, 20060930, 20061231, 20070331, 20070630, 20070930, 20080630, 20080930, 20081231]),
     'year': pd.Series([2006, 2006, 2006, 2006, 2007, 2007, 2007, 2008, 2008, 2008]),
     'Q': pd.Series([1, 2, 3, 4, 1, 2, 3, 2, 3, 4]),
     'EquityEoQ': pd.Series([112, 223, 333, 445, 126, 251, 376, 291, 291, 503]),
     'NewEqRight': pd.Series([112, 111, 110, 112, 126, 125, 125, np.nan, 0, 212, ])})
# df2=df2[['FirmID','RSSD9999', 'year', 'Q', 'EquityEoQ','NewEqRight']]
print df2

df2['EquityEoLastQ'] = df2.groupby(['FirmID'])['EquityEoQ'].shift(1)
print df2
df2['NewEqWrong'] = df2['EquityEoQ'] - df2['EquityEoLastQ']
df2.loc[df2['Q'] == 1, 'NewEqWrong'] = df2.loc[df2['Q'] == 1, 'EquityEoQ']
print df2

index = pd.date_range('10/1/1999', periods=1100)

# ===========test shift==============

df = pd.DataFrame({"C1": [1, 2, 3]}, ['ro1', 'row2', 'row3'])
df['C2'] = df.shift(1)["C1"]
print df
print df.shift(1)

print(np.random.randint(8))
print(np.random.randn(8, 18))

print ("---------------------------------")
periods = pd.date_range('2016-03-01', periods=8, freq='3M')
finance_df = pd.DataFrame(np.random.randint(1, 10, size=(2, 8)), columns=periods, index=['cost', 'income'])
print finance_df
delta_finance_df = finance_df.diff(axis=1)
print delta_finance_df


def transFun(col):
    period = col.name
    if str(period).split('-')[1] == '03':
        return finance_df[col.name]
    else:
        return col


print delta_finance_df.apply(transFun, axis=0)

df = pd.DataFrame([[1, 2, 3], [4, 5, 6]], columns=['a', 'b', 'c'])


def rowFunc(row):
    print(row.name)
    return row * 2


def columnFunc(col):
    # print(col.name)
    return col


print df
print df.apply(rowFunc, axis=1)
#print df.apply(np.square, axis=1)
#print df.apply(columnFunc, axis=0)

#print list('bde')

# print df
df = pd.DataFrame([[1,2,3,4,5], [5,6,7,8,9], [9,10,11,12,12]], index = ['income', 'cost', 'total'],
                  columns = [ '2016-03', '2016-06', '2016-09', '2016-12','2017-03'])
df = df.transpose()
print df
df['year_average_income'] = (df['income'] + df['income'].shift(3))/2
print "==================="
df.iloc[0:3, df.columns.get_loc('year_average_income')] = df.iloc[0:3, df.columns.get_loc('income')]
print df
print df['income']
print df['income']/df['total']
print df;

#https://stackoverflow.com/questions/44560716/using-numpy-and-pandas-how-to-calculate-percentage-and-using-criteria-and-give-i

print np.arange(10)
df = pd.DataFrame(np.arange(10).reshape(-1, 2), columns=['A', 'B'])
print df % 3 == 0
#https://stackoverflow.com/questions/19798153/difference-between-map-applymap-and-apply-methods-in-pandas
df = df.where(df > 3 , -df)


columns = ['a', 'b', 'c']

print 'a' in columns
print 'e' in columns
print df.columns.isin(['A', 'B']).any()

df = pd.DataFrame([[1,2,3,4,5], [5,6,7,8,9], [9,10,11,12,12]], index = ['income', 'cost', 'total'],
                  columns = [ '2016-03', '2016-06', '2016-09', '2016-12','2017-03'])
print df.reindex([ 'cost', 'income', 'n'])
print df
print df.iloc[1]
print df[0:1]


print  ("empty:" + str(df['2016-12'].empty))

print 'income' in df.index